package com.dao.impl;

import com.dao.TicketDao;
import com.pojo.Flight;
import com.pojo.Ticket;
import com.pojo.User;
import com.util.JNDIUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * dao层 通过写sql语句 拼接sql 查询语句库并返回数据
 */
public class TicketDaoImpl implements TicketDao {

    /**
     * 管理员查询所有机票
     * @param page
     * @param limit
     * @return
     */
    @Override
    public List<Ticket> selectAllTicket(String page, String limit) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JNDIUtils.getConnection();
            //连表查询的sql语句
            String sql = "select ticket.*, flight.f_number,flight.start_date,flight.start_time,flight.start_add,flight.target_add, " +
                    "`user`.username,user.id_number from `ticket`,flight,`user` where flight_id = f_id and user_id = u_id order by `create_time` ASC limit ?,?";
            ps = conn.prepareStatement(sql);
            //填充分页参数
            ps.setInt(1,(Integer.parseInt(page)-1)*Integer.parseInt(limit));//（页数-1）* 每页数量
            ps.setInt(2,Integer.parseInt(limit)); //每页的数量
            rs = ps.executeQuery();
            List list = new ArrayList();
            while (rs.next()) {
                Ticket ticket = new Ticket();
                User user = new User();
                Flight flight = new Flight();
                //封装用户信息
                user.setName(rs.getString("username"));//旅客姓名
                user.setIdNumber(rs.getString("id_number"));//身份证
                //封装航班信息
                flight.setFlightNumber(rs.getString("f_number"));//航班号
                flight.setStartDate(rs.getDate("start_date").toString());//起飞日期
                flight.setStartTime(rs.getTime("start_time").toString());//起飞时间
                flight.setStartAdd(rs.getString("start_add"));//始发地
                flight.setTargetAdd(rs.getString("target_add"));//目的地

                //封装机票信息
                ticket.setTicketId(rs.getInt("ticket_id"));//机票id
                ticket.setCabin(rs.getString("cabin"));//机舱 商务舱 经济舱
                ticket.setPrice(rs.getDouble("price"));//机票价格
                ticket.setOffer(rs.getString("offer"));//打折信息
                ticket.setCreateTime(rs.getTimestamp("create_time").toString());
                //封装成ticket对象返回
                ticket.setUser(user);
                ticket.setFlight(flight);
                list.add(ticket);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.close(conn,ps,rs);
        }
        return null;
    }

    /**
     * 条件查询机票信息
     * @param username
     * @param startAdd
     * @param createTime
     * @param page
     * @param limit
     * @return
     */
    @Override
    public List<Ticket> companySelectTicket(String username, String startAdd, String createTime, String page, String limit) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JNDIUtils.getConnection();
            String sql = "select ticket.*, flight.f_number,flight.start_date,flight.start_time,flight.start_add,flight.target_add, `user`.username,user.id_number " +
                    "from `ticket`,flight,`user` where flight_id = f_id and user_id = u_id and " +
                    "username like ? and `start_add` like ? and create_time like ? order by `create_time` ASC limit ?,?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+username+"%");
            ps.setString(2,"%"+startAdd+"%");
            ps.setString(3,"%"+createTime+"%");
            ps.setInt(4,(Integer.parseInt(page)-1)*Integer.parseInt(limit));//（页数-1）* 每页数量
            ps.setInt(5,Integer.parseInt(limit)); //每页的数量
            rs = ps.executeQuery();
            List list = new ArrayList();
            while (rs.next()) {
                Ticket ticket = new Ticket();
                User user = new User();
                Flight flight = new Flight();
                //封装用户信息
                user.setName(rs.getString("username"));//旅客姓名
                user.setIdNumber(rs.getString("id_number"));//身份证
                //封装航班信息
                flight.setFlightNumber(rs.getString("f_number"));//航班号
                flight.setStartDate(rs.getDate("start_date").toString());//起飞日期
                flight.setStartTime(rs.getTime("start_time").toString());//起飞时间
                flight.setStartAdd(rs.getString("start_add"));//始发地
                flight.setTargetAdd(rs.getString("target_add"));//目的地

                //封装机票信息
                ticket.setTicketId(rs.getInt("ticket_id"));//机票id
                ticket.setCabin(rs.getString("cabin"));//机舱 商务舱 经济舱
                ticket.setPrice(rs.getDouble("price"));//机票价格
                ticket.setOffer(rs.getString("offer"));//打折信息
                ticket.setCreateTime(rs.getTimestamp("create_time").toString());
                //封装成ticket对象返回
                ticket.setUser(user);
                ticket.setFlight(flight);
                list.add(ticket);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.close(conn,ps,rs);
        }
        return null;
    }

    /**
     * 添加机票
     * @param ticket
     */
    @Override
    public void insertTicket(Ticket ticket) {
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JNDIUtils.getConnection();
            String sql = "insert into `ticket` (flight_id, user_id,  cabin, price, offer,`create_time`) values (?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,ticket.getFlightId());
            ps.setInt(2,ticket.getUserId());
            ps.setString(3,ticket.getCabin());
            ps.setDouble(4,ticket.getPrice());
            ps.setString(5,"0.95");
            ps.setString(6, LocalDateTime.now().toString());
            ps.executeUpdate();

        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            JNDIUtils.close(conn,ps,null);
        }
    }

    /**
     * 查询所有机票数量
     * @return
     */
    @Override
    public int selectAllTicketCount() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JNDIUtils.getConnection();
            String sql = "select count(*) from ticket";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next())
                return rs.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.close(conn,ps,rs);
        }
        return 0;
    }

    /**
     * 查询指定条件的机票数量
     * @param username
     * @param startAdd
     * @param createTime
     * @return
     */
    @Override
    public int selectTicketCountByCondition(String username, String startAdd, String createTime) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JNDIUtils.getConnection();
            String sql = "select count(*) from `ticket`,flight,`user` where flight_id = f_id and user_id = u_id and " +
                    "username like ? and `start_add` like ? and create_time like ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"%"+username+"%");
            ps.setString(2,"%"+startAdd+"%");
            ps.setString(3,"%"+createTime+"%");
            rs = ps.executeQuery();
            if (rs.next())
                return rs.getInt(1);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.close(conn,ps,rs);
        }
        return 0;
    }


}
